package Dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.HashSet;
import java.util.Map;
import java.util.Set;

import org.aspectj.weaver.patterns.ThisOrTargetAnnotationPointcut;
import org.springframework.dao.DataAccessException;
import org.springframework.http.converter.feed.RssChannelHttpMessageConverter;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.ResultSetExtractor;

import com.fasterxml.jackson.databind.deser.std.StringArrayDeserializer;

import Bean.BeanBook;
import Bean.BeanOrder;
import Bean.BeanOrderDetail;
import Bean.BeanRating;



public class RatingDao {
	private JdbcTemplate jdbcTemplate;

	public JdbcTemplate getJdbcTemplate() {
		return jdbcTemplate;
	}

	public void setJdbcTemplate(JdbcTemplate jdbcTemplate) {
		this.jdbcTemplate = jdbcTemplate;
	}
	public void ratingadd(String userid, String bookid,int score) throws Exception {
		String sql="select * from rating where userid=? and bookid=?";
		BeanRating rating=this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanRating>() {
			@Override
			public BeanRating extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while (rs.next()) {
					BeanRating rating = new BeanRating();
					return rating;
				}
				return null;
			}
		},userid,bookid);
		
		if(rating==null) {
			sql="insert into rating(userid,bookid,score) values(?,?,?)";
			this.jdbcTemplate.update(sql,new Object[] {userid,bookid,score});
		}	
		else {
			sql="update rating set score=? where userid=? and bookid=?";
			this.jdbcTemplate.update(sql,new Object[] {score,userid,bookid});
		}		
	}
	public ArrayList<BeanRating> getratings(String userid) throws Exception {
		ArrayList<BeanRating> ratings = new ArrayList<>();
		Set<String>bookids=new HashSet<>();
		String sql="select bookid,score from rating where userid=?";
		this.jdbcTemplate.query(sql, new ResultSetExtractor<BeanRating>() {
			@Override
			public BeanRating extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while (rs.next()) {
					BeanRating rating = new BeanRating();
					rating.setBookid(rs.getString(1));
					bookids.add(rs.getString(1));
					rating.setRating(rs.getInt(2));
					ratings.add(rating);
				}
				return null;
			}
		},userid);
		String strbookids="";
		for(String bookid:bookids) {
			bookid="'"+bookid+"'";
			if(strbookids=="") strbookids=bookid;
			else strbookids+=","+bookid;
		}
		String selectStatement="select book_id,title,author,publisher,publishing_time,book_price,category_id,inventory,description,url from books where book_id in ("+strbookids+") order by book_id desc";
		
		Map<String, BeanBook>bookMap=new HashMap<>();
		this.jdbcTemplate.query(selectStatement, new ResultSetExtractor<BeanBook>() {
			@Override
			public BeanBook extractData(ResultSet rs) throws SQLException, DataAccessException {
				// TODO Auto-generated method stub
				while(rs.next()) {
					BeanBook bookDetails=new BeanBook();
					bookDetails.setBook_id(rs.getString(1)); 
					bookDetails.setTitle(rs.getString(2));
					bookDetails.setAuthor(rs.getString(3));
					bookDetails.setPublisher(rs.getString(4));
					bookDetails.setPublishing_time(rs.getInt(5));
					bookDetails.setBook_price(rs.getFloat(6));
					bookDetails.setCategory_id(rs.getInt(7));
					bookDetails.setInventory(rs.getInt(8));
					bookDetails.setDescription(rs.getString(9));
					bookDetails.setUrl(rs.getString(10));
					bookMap.put(bookDetails.getBook_id(), bookDetails);
				}
				return null;
			}
		});	
		for(BeanRating rating:ratings) {
			rating.setBook(bookMap.get(rating.getBookid()));
		}
		return ratings;
	}
}
